package com.wxtf.platform.main.sygl;

import java.io.File;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: pregnancy_recordAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.sygl</li>
 * <li>Description: 怀孕</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 24, 2012 3:38:25 PM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class pregnancy_recordAction extends LogicBase implements
		CommonsCallProvider {
	private static final Log log = LogFactory
			.getLog(pregnancy_recordAction.class);

	public pregnancy_recordAction(String jsonArray) {

	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if ("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if ("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if ("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if ("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if ("updateOption".equals(this.getMethodName())) {
				msg = this.updateOption(this.getLogicData());
			} else if ("saveneo".equals(this.getMethodName())) {
				msg = this.insertNeo(this.getLogicData());
			} else if ("updateneo".equals(this.getMethodName())) {
				msg = this.updateNeo(this.getLogicData());
			} else if ("updateOptionneo".equals(this.getMethodName())) {
				msg = this.updateOptionNeo(this.getLogicData());
			} else if ("updatLateData".equals(this.getMethodName())) {
				msg = this.updatLateData(this.getLogicData());
			} else if ("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			} else if ("listAll".equals(this.getMethodName())) {// 选人列表
				msg = this.listAll(this.getLogicData());
				//return msg;
			} else if("selectDetail".equals(this.getMethodName())) {
				msg = this.selectDetail(this.getLogicData());
				//return msg;
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if (msg == null || "[null]".equals(msg) || "null".equals(msg)) {
				msg = "\"\"";
			}
			if (msg.toString().startsWith("{")
					|| msg.toString().startsWith("[")) {
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch (Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		
		sql = " FROM tbe_pregnancy_record p LEFT JOIN tbe_neonate_main n ON p.tbepr_id=n.tbepr_id LEFT JOIN tb_family_base f ON f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id where e.tbe_status not in('102-5','102-4','102-3') and (n.reserve5 is NULL or n.reserve5 <> '1')";
		// 查询时夫妻双方都是一汽的职工显示妻子信息，只有一个是一汽职工只显示是一汽职工的那个人,不论男女【管理的情况必须为已婚】
		//新生儿reserve5状态不等于1，等于1说明不是新生儿
		JSONArray array = JSONArray.fromObject(jsonArray);
		if (array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");

			if ("init".equals(types)) {// 初始查询
				sql += " and e.org_id in (" + user.getCurSubOrgId() + ")";
			} else if ("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");// 机构ID串
				String tbe_work_permit = jsonObject.getString("tbe_work_permit");// 身份证号
				String tbe_spell_code = jsonObject.getString("tbe_spell_code");// 名称
				String tbepr_ptime = jsonObject.getString("tbepr_ptime");// 怀孕时间
				String tbenm_plan_status = jsonObject.getString("tbenm_plan_status");// 计划状态
				String tbe_is_late_birth = jsonObject.getString("tbe_is_late_birth");// 是否晚婚+晚育

				StringBuffer buff = new StringBuffer();
				if (StringUtils.isNotBlank(orgIds)) {
					buff.append(" and e.org_id in (").append(
							user.getCurSubOrgIds(orgIds)).append(")");
				}

				if (StringUtils.isNotBlank(tbe_work_permit)) {
					buff.append(" and e.tbe_idcard like '%").append(
							tbe_work_permit).append("%'");
				}

				if (StringUtils.isNotBlank(tbe_spell_code)) {
					buff.append(" and e.tbe_name like '%").append(
							tbe_spell_code.toUpperCase()).append("%'");
				}

				if (StringUtils.isNotBlank(tbepr_ptime)) {
					buff.append(" and p.tbepr_ptime >= '").append(tbepr_ptime)
							.append("'");
//					 buff.append(" and p.tbepr_ptime <= '").append(tbepr_ptime).append(" 23:59:59'");
				}

				if (StringUtils.isNotBlank(tbenm_plan_status)) {
					if (!tbenm_plan_status.equals("2")
							&& tbenm_plan_status != "") {
						buff.append(" and n.tbenm_plan_status = '").append(
								tbenm_plan_status).append("'");
					}
				}

				if (StringUtils.isNotBlank(tbe_is_late_birth)) {
					if (!tbe_is_late_birth.equals("2")&& tbe_is_late_birth != "") {
						buff.append(" and n.reserve4  = '").append(tbe_is_late_birth).append("'");
					}
				}

				sql += buff.toString();
			}
		}

		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if (sortname != null && sortname != null) {
			if(sortname.substring(0, 4).equals("tbep"))
			{
				sql += " order by p." + sortname + " " + sortorder;
			}
			else if(sortname.substring(0, 4).equals("tbfi"))
			{
				sql += " order by f." + sortname + " " + sortorder;
			}
			else if("reserve4".equals(sortname))
			{
				sql += " order by n." + sortname + " " + sortorder;
			}
			else if(sortname.substring(0, 4).equals("tben"))
			{
				sql += " order by n." + sortname + " " + sortorder;
			}
			else
			{
				sql += " order by e." + sortname + " " + sortorder;
			}
			
		}else{
			sql+=" order by tbepr_option asc,p.last_modify_time desc";
		}
		
		String countSql = "select count(*)" + sql;
		String querySql = "SELECT f.tbfi_id,e.org_id,e.tbe_id,e.tbe_idcard,e.tbe_sex,e.tbe_birthday,(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,e.tbe_name,f.tbfi_phone,f.tbfi_address,f.tbfi_spouse_name,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,p.tbepr_id,p.tbepr_option,p.tbepr_ptime,n.tbenm_id,n.tbenm_plan_status,n.tbenm_gravida_times,n.tbenm_gravida_num,n.tbenm_birth_time,e.tbe_is_late_birth,p.tbepr_memo,n.tbenm_memo,e.reserve1,n.reserve2,n.reserve3,n.reserve4,n.reserve5,p.create_by,p.create_time,p.last_modify_by,p.last_modify_time" + sql;
		String[] fields = { "tbfi_id", "org_id", "tbe_id", "tbe_idcard","tbe_sex","tbe_birthday","tbe_nation",
				"tbe_name", "tbfi_phone", "tbfi_address", "tbfi_peo_num",
				"tbfi_children_num", "tbfi_children_mnum",
				"tbfi_children_fnum", "tbepr_id", "tbepr_option",
				"tbepr_ptime", "tbenm_id", "tbenm_plan_status",
				"tbenm_gravida_times", "tbenm_gravida_num", "tbenm_birth_time",
				"tbe_is_late_birth", "tbepr_memo","tbenm_memo",
				"reserve1", "reserve2", "reserve3", "reserve4",
				"reserve5", "create_by", "create_time", "last_modify_by",
				"last_modify_time" };

//		System.out.println(querySql);
		String strJson = AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(),null);
		int pregnancying = 0;
		int pregnancyed = 0;
		int pregnancy = 0;
		int plan = 0;
		int plan1 = 0;
		int plan2 = 0;
		int late = 0;
//		String pre1="select count(*)" + sql+"and tbepr_option='0'";//怀孕
//		String pre2="select count(*)" + sql+"and tbepr_option='1'";//等孕
//		System.out.println(pl1);
//		pregnancyed=AccessDataBaseUtil.queryForInt(pre1, null);
//		pregnancying=AccessDataBaseUtil.queryForInt(pre2, null);
//		pregnancy = pregnancying+pregnancyed;
//		return strJson.substring(0, strJson.indexOf(","))+",\"pregnancying\":\"" + pregnancying+ "\",\"pregnancyed\":\"" + pregnancyed + "\","+ "\"pregnancy\":\"" +pregnancy+"\"," + "\"plan\":\"" + plan + "\",\"plan1\":\"" + plan1+ "\",\"plan2\":\"" + plan2 + "\",\"late\":\"" + late+ "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
//		System.out.println(System.currentTimeMillis());
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, querySql,null);
		if (list==null){
			return strJson;
		} else {
			if (list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {// 行
					Map<String, String> map = list.get(i);
					if (map.get("tbepr_option").equals("0")) {
						pregnancyed += 1;
					}
					if (map.get("tbepr_option").equals("1")) {
						pregnancying += 1;
					}
					//计划内一胎和计划内二胎
					if (map.get("tbenm_plan_status").equals("0")) {
						String num = map.get("tbenm_gravida_num");// 胎数
						num = num.equals("") ? "0" : num;
						plan1 += Integer.parseInt(num);
						plan += Integer.parseInt(num);
						if (map.get("reserve4").equals("1")) {//晚婚+晚育
//							if(!listlate.contains(map.get("tbe_id")))
//							{
//								listlate.add(map.get("tbe_id"));
//							}
							late += Integer.parseInt(num);;
						}
					}
					if (map.get("tbenm_plan_status").equals("1")) {
						String num = map.get("tbenm_gravida_num");// 胎数
						num = num.equals("") ? "0" : num;
						plan2 += Integer.parseInt(num);
						plan += Integer.parseInt(num);
					}
				}
				pregnancy = pregnancying+pregnancyed;
				return strJson.substring(0, strJson.indexOf(","))+",\"pregnancying\":\"" + pregnancying+ "\",\"pregnancyed\":\"" + pregnancyed + "\","+ "\"pregnancy\":\"" +pregnancy+"\"," + "\"plan\":\"" + plan + "\",\"plan1\":\"" + plan1+ "\",\"plan2\":\"" + plan2 + "\",\"late\":\"" + late+ "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
			
			} else {
				return strJson;
			}
		}

	}

	@Override
	public Object insert(Object jsonArray) throws Exception {

		String[] fields = { "tbepr_id", "org_id", "tbfi_id", "tbepr_option",
				"tbepr_ptime", "tbepr_memo", "reserve1", "reserve2",
				"reserve3", "reserve4", "reserve5", "create_by", "create_time",
				"last_modify_by", "last_modify_time" };

		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into tbe_pregnancy_record(tbepr_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		String tbfi_id = jsonObject.getString("tbfi_ids");
		tbfi_id = tbfi_id.equals("") ? jsonObject.getString("tbfi_id") : tbfi_id;
		obj[2] = tbfi_id;
		String curTime = FormatUtil.getCurentDateTime();
		obj[11] = user.getUserId();
		obj[12] = curTime;
		obj[13] = user.getUserId();
		obj[14] = curTime;

		for (int i = 0; i < obj.length; i++) {
			buff2.append("'").append(obj[i]).append("',");
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		
		// 添加怀孕信息时也可以更新家庭人数信息
		String m = jsonObject.getString("tbfi_children_mnums");// 男孩数【用户直接修改当前的家庭男孩数】
		String f = jsonObject.getString("tbfi_children_fnums");// 女孩数
		m = m.equals("") ? "0" : m;
		f = f.equals("") ? "0" : f;
		int c = Integer.parseInt(m) + Integer.parseInt(f);// 家庭子女数
		//判断该家庭是否曾经办过独生子女证，如果家庭子女数超过一个，提示不可以修改，要求先去退证
		if(c>1)
		{
			String strOnly="SELECT COUNT(*) FROM tbe_only_child_record WHERE reserve2<>'1' and tbfi_id in (SELECT tbfi_id FROM tb_family_base where tbe_id = '"+jsonObject.getString("tbe_id")+"' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
			if(AccessDataBaseUtil.queryForInt(strOnly, null)>0)
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭办理过独生子女证,请先退证,然后修改家庭信息\"}";
			}
		}
		//修改同家庭的怀孕信息，注销同家庭的怀孕信息
		String sqlpenFam = " update tbe_pregnancy_record set tbepr_option='2',tbepr_ptime='' where tbfi_id = '" + tbfi_id + "' ";
		
		String sqlFam = "update tb_family_base set tbfi_children_mnum = " +m+",tbfi_children_fnum = " + f + ",tbfi_children_num=" + c
				+ " where (tbe_id = '" + jsonObject.getString("tbe_id") + "' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";

		String[] sqls = {sqlpenFam, sql1,sqlFam };
		//System.out.println(sqlFam);
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功:pre" + obj[0] + "\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		String[] fields = { "tbepr_id", "org_id", "tbfi_id", "tbepr_option",
				"tbepr_ptime", "tbepr_memo", "reserve1", "reserve2",
				"reserve3", "reserve4", "reserve5", "create_by", "create_time",
				"last_modify_by", "last_modify_time" };
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		String curTime = FormatUtil.getCurentDateTime();

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		String tbfi_id = jsonObject.getString("tbfi_ids");
		tbfi_id = tbfi_id.equals("") ? jsonObject.getString("tbfi_id") : tbfi_id;
		buff.append("update tbe_pregnancy_record set ");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			if(j==2){ obj[1] = tbfi_id; }
			obj[12] = user.getUserId();
			obj[13] = curTime;
			buff.append(fields[j]).append(" = ").append("'").append(obj[j - 1])
					.append("',");
		}
		buff.deleteCharAt(buff.length() - 1).append(
				" where tbepr_id = '" + jsonObject.getString("tbepr_id") + "'");
		
		// 添加怀孕信息时也可以更新家庭人数信息
		String m = jsonObject.getString("tbfi_children_mnums");// 男孩数【用户直接修改当前的家庭男孩数】
		String f = jsonObject.getString("tbfi_children_fnums");// 女孩数
		m = m.equals("") ? "0" : m;
		f = f.equals("") ? "0" : f;
		int c = Integer.parseInt(m) + Integer.parseInt(f);// 家庭子女数
		//判断该家庭是否曾经办过独生子女证，如果家庭子女数超过一个，提示不可以修改，要求先去退证
		if(c>1)
		{
			String strOnly="SELECT COUNT(*) FROM tbe_only_child_record WHERE reserve2<>'1' and tbfi_id in (SELECT tbfi_id FROM tb_family_base where tbe_id = '"+jsonObject.getString("tbe_id")+"' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
			if(AccessDataBaseUtil.queryForInt(strOnly, null)>0)
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭办理过独生子女证,请先退证,然后修改家庭信息\"}";
			}
		}
		//修改同家庭的怀孕信息，注销同家庭的怀孕信息
		String sqlpenFam = " update tbe_pregnancy_record set tbepr_option='2',tbepr_ptime='' where tbfi_id = '" + tbfi_id + "' ";
		
		String sqlFam = "update tb_family_base set tbfi_children_mnum = " + m
				+ ",tbfi_children_fnum = " + f + ",tbfi_children_num=" + c
				+ " where (tbe_id = '" + jsonObject.getString("tbe_id") + "' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
		//System.out.println(sqlFam);
		String[] sqls = {sqlpenFam,buff.toString(),sqlFam };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");

		String sqldel = "";
		for (int i = 0; i < ids.length - 2; i = i + 3) {
			sqldel = sqldel + "delete from tbe_neonate_main where tbenm_id = '"
			+ ids[i + 1].split(":")[0] + "'" + ",";
			
			sqldel = sqldel
					+ "delete from tbe_pregnancy_record where tbepr_id = '"
					+ ids[i].split(":")[0] + "'" + ",";

			sqldel = sqldel
					+ "delete from tbe_neonate_detail where tbend_id = '"
					+ ids[i + 2].split(":")[0] + "'" + ",";
		}

		String[] sqls = sqldel.split(",");
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"删除成功\"";
	}

	public Object updateOption(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbepr_id = jsonObject.getString("tbepr_id");
		String[] tbepr_ids = { tbepr_id };
		sql = "update tbe_pregnancy_record set tbepr_option = 2 where tbepr_id = ?";
		AccessDataBaseUtil.executeSQLUpdate(sql, tbepr_ids);
		return "\"注销成功\"";
	}

	// /保存新生儿信息
	public Object insertNeo(Object jsonArray) throws Exception {
		String[] fields = { "tbenm_id", "tbepr_id", "org_id",
				"tbenm_plan_status", "tbenm_gravida_times",
				"tbenm_gravida_num", "tbenm_birth_time", "tbenm_memo",
				"reserve1", "reserve2", "reserve3", "reserve4", "reserve5",
				"create_by", "create_time", "last_modify_by",
				"last_modify_time" };

		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));

		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		String tbfi_id = jsonObject.getString("tbfi_ids");
		tbfi_id = tbfi_id.equals("") ? jsonObject.getString("tbfi_id") : tbfi_id;
		String curTime = FormatUtil.getCurentDateTime();
		String id = AccessDataBaseUtil.getPKUUID();
		//同一个家庭计划内一胎和计划内二胎只可以添加一次，不可重复添加
		String querySql = " SELECT tbenm_plan_status from tbe_neonate_main where tbepr_id in (select tbepr_id from tbe_pregnancy_record where tbfi_id = ?)";
		List<Object> list = AccessDataBaseUtil.query4List(querySql,new Object[] {tbfi_id});
		int plan1=0;int plan2=0;
		if(list.size()>0)
		{
			for (int i = 0; i < list.size(); i++)
			{
				if(list.get(i).toString().equals("0"))
				{
					plan1++;
				}
				if(list.get(i).toString().equals("1"))
				{
					plan2++;
				}
			}
			
		}
		if(plan1>0&&plan2>0)
		{
			//提示不允许添加
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内一胎和计划内二胎，不允许再次添加\"}";
		}
		else if(plan1>0&&plan2==0)
		{
			//提示允许添加计划内二胎
			if(jsonObject.getString("tbenm_plan_status").equals("0"))
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内一胎，不允许再次添加计划内一胎\"}";
			}

		}
		else if(plan1==0&&plan2>0)
		{
			if(jsonObject.getString("tbenm_plan_status").equals("1"))
			{
				//提示允许添加计划内一胎
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内二胎，不允许再次添加计划内二胎\"}";
			}

		}
			//某人的怀孕状态需要随着新生儿登记而自动注销
			String cancelPre = "update tbe_pregnancy_record set tbepr_option='2',tbepr_ptime='' where tbfi_id = '"+tbfi_id+"'";
			
			//需要先判断怀孕情况
			String tbepr_id = jsonObject.getString("tbepr_id");//如何怀孕信息已添加，不用添加，否则添加一条怀孕状态为"无"的怀孕信息
			String pre="";
			if(tbepr_id.equals(""))
			{
				pre="insert into tbe_pregnancy_record(tbepr_id,org_id,tbfi_id,tbepr_option,reserve1,create_by,create_time,last_modify_by,last_modify_time)values" +
						"('"+id+"','"+jsonObject.getString("org_id")+"','"+tbfi_id+"',2,'"+jsonObject.getString("reserve1")+"','"+user.getUserId()+"','"+curTime+"','"+user.getUserId()+"','"+curTime+"')";
				tbepr_id = id;
			}
			
			buff.append("insert into tbe_neonate_main(tbenm_id");
			Object[] obj = new Object[fields.length];
			for (int j = 1; j < fields.length; j++) {
				obj[j] = jsonObject.getString(fields[j]);
				buff.append(",").append(fields[j]);
			}
			buff.append(") values(");
			obj[0] = AccessDataBaseUtil.getPKUUID();
			obj[1] = tbepr_id;
			obj[13] = user.getUserId();
			obj[14] = curTime;
			obj[15] = user.getUserId();
			obj[16] = curTime;
	
			for (int i = 0; i < obj.length; i++) {
				buff2.append("'").append(obj[i]).append("',");
			}
			buff2.deleteCharAt(buff2.length() - 1);
			String sql1 = buff.append(buff2.toString()).append(")").toString();
			
			//新生儿
			String reserve2 = jsonObject.getString("reserve2");// 新生的男孩数
			String reserve3 = jsonObject.getString("reserve3");// 新生的女孩数
			reserve2 = reserve2.equals("") ? "0" : reserve2;
			reserve3 = reserve3.equals("") ? "0" : reserve3;
			
			// 更新家庭人数信息
			String m = jsonObject.getString("tbfi_children_mnum");// 男孩数【用户直接修改当前的家庭男孩数】
			String f = jsonObject.getString("tbfi_children_fnum");// 女孩数
			String ms = jsonObject.getString("tbfi_children_mnums");// 原来新生男孩数【用户直接修改当前的家庭男孩数】
			String fs = jsonObject.getString("tbfi_children_fnums");// 原来新生女孩数
			ms = ms.equals("") ? "0" : ms;
			fs = fs.equals("") ? "0" : fs;
			
			int mnum = m.equals("") ? 0 : Integer.parseInt(m)+Integer.parseInt(reserve2)-Integer.parseInt(ms);
			int fnum = m.equals("") ? 0 : Integer.parseInt(f)+Integer.parseInt(reserve3)-Integer.parseInt(fs);
			mnum = mnum<0 ? 0 : mnum;
			fnum = fnum<0 ? 0 : fnum;
			int c = mnum+fnum;// 家庭子女数
			//判断该家庭是否曾经办过独生子女证，如果家庭子女数超过一个，提示不可以修改，要求先去退证
			if(c>1)
			{
				String strOnly="SELECT COUNT(*) FROM tbe_only_child_record WHERE reserve2<>'1' and tbfi_id in (SELECT tbfi_id FROM tb_family_base where tbe_id = '"+jsonObject.getString("tbe_id")+"' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
//				System.out.println(strOnly);
				if(AccessDataBaseUtil.queryForInt(strOnly, null)>0)
				{
					return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭办理过独生子女证,请先退证,然后修改家庭信息\"}";
				}
			}
			String sqlFam = "update tb_family_base set tbfi_children_mnum = " + mnum
					+ ",tbfi_children_fnum = " + fnum + ",tbfi_children_num=" + c
					+ " where (tbe_id = '" + jsonObject.getString("tbe_id") + "' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
	
			String[] sqls;
			if(pre.equals(""))
			{
				sqls =new String[]{ cancelPre,sql1,sqlFam };
			}
			else{sqls = new String[]{ cancelPre,pre,sql1,sqlFam };}
			
			//System.out.println("pre:"+pre);
			//System.out.println(sql1);
			//System.out.println(sqlFam);
			AccessDataBaseUtil.executeSQLUpdate(sqls);
			return "\"保存成功:neo" + obj[0]+"|"+obj[1]+"\"";
		
	}

	// /修改新生儿信息
	public Object updateNeo(Object jsonArray) throws Exception {
		String[] fields = { "tbenm_id", "tbepr_id", "org_id",
				"tbenm_plan_status", "tbenm_gravida_times",
				"tbenm_gravida_num", "tbenm_birth_time", "tbenm_memo",
				"reserve1", "reserve2", "reserve3", "reserve4", "reserve5",
				"create_by", "create_time", "last_modify_by",
				"last_modify_time" };
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		String curTime = FormatUtil.getCurentDateTime();

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		
		//新生儿
		String reserve2 = jsonObject.getString("reserve2");// 新生的男孩数
		String reserve3 = jsonObject.getString("reserve3");// 新生的女孩数
		reserve2 = reserve2.equals("") ? "0" : reserve2;
		reserve3 = reserve3.equals("") ? "0" : reserve3;

		// 更新家庭人数信息
		String m = jsonObject.getString("tbfi_children_mnum");// 男孩数【用户直接修改当前的家庭男孩数】
		String f = jsonObject.getString("tbfi_children_fnum");// 女孩数
		String ms = jsonObject.getString("tbfi_children_mnums");// 原来新生男孩数【用户直接修改当前的家庭男孩数】
		String fs = jsonObject.getString("tbfi_children_fnums");// 原来新生女孩数
		ms = ms.equals("") ? "0" : ms;
		fs = fs.equals("") ? "0" : fs;
		
		int mnum = m.equals("") ? 0 : Integer.parseInt(m)+Integer.parseInt(reserve2)-Integer.parseInt(ms);
		int fnum = m.equals("") ? 0 : Integer.parseInt(f)+Integer.parseInt(reserve3)-Integer.parseInt(fs);
		mnum = mnum<0 ? 0 : mnum;
		fnum = fnum<0 ? 0 : fnum;
		int c = mnum+fnum;// 家庭子女数
		//判断该家庭是否曾经办过独生子女证，如果家庭子女数超过一个，提示不可以修改，要求先去退证
		if(c>1)
		{
			String strOnly="SELECT COUNT(*) FROM tbe_only_child_record WHERE reserve2<>'1' and tbfi_id in (SELECT tbfi_id FROM tb_family_base where tbe_id = '"+jsonObject.getString("tbe_id")+"' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
			
			if(AccessDataBaseUtil.queryForInt(strOnly, null)>0)
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭办理过独生子女证,请先退证,然后修改家庭信息\"}";
			}
		}
		String tbfi_id = jsonObject.getString("tbfi_ids");
		tbfi_id = tbfi_id.equals("") ? jsonObject.getString("tbfi_id") : tbfi_id;
		
		//同一个家庭计划内一胎和计划内二胎只可以添加一次，不可重复
		String querySql = " SELECT tbenm_plan_status from tbe_neonate_main where tbepr_id in (select tbepr_id from tbe_pregnancy_record where tbfi_id = ?) and tbenm_id <> '" + jsonObject.getString("tbenm_id") + "'";
		List<Object> list = AccessDataBaseUtil.query4List(querySql,new Object[] {tbfi_id});
		int plan1=0;int plan2=0;
		if(list.size()>0)
		{
			for (int i = 0; i < list.size(); i++)
			{
				if(list.get(i).toString().equals("0"))
				{
					plan1++;
				}
				if(list.get(i).toString().equals("1"))
				{
					plan2++;
				}
			}
			
		}
		if(plan1>0&&plan2>0)
		{
			//提示不允许添加
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内一胎和计划内二胎，不允许再次添加\"}";
		}
		else if(plan1>0&&plan2==0)
		{
			//提示允许添加计划内二胎
			if(jsonObject.getString("tbenm_plan_status").equals("0"))
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内一胎，不允许再次添加计划内一胎\"}";
			}

		}
		else if(plan1==0&&plan2>0)
		{
			if(jsonObject.getString("tbenm_plan_status").equals("1"))
			{
				//提示允许添加计划内一胎
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了计划内二胎，不允许再次添加计划内二胎\"}";
			}

		}
		
		buff.append("update tbe_neonate_main set ");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			obj[14] = user.getUserId();
			obj[15] = curTime;
			buff.append(fields[j]).append(" = ").append("'").append(obj[j - 1])
					.append("',");
		}
		buff.deleteCharAt(buff.length() - 1).append(
				" where tbenm_id = '" + jsonObject.getString("tbenm_id") + "'");
		
		String sqlFam = "update tb_family_base set tbfi_children_mnum = " + mnum
		+ ",tbfi_children_fnum = " + fnum + ",tbfi_children_num=" + c
		+ " where (tbe_id = '" +jsonObject.getString("tbe_id")+ "' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";
		//System.out.println(sqlFam);
		String[] sqls = { buff.toString(), sqlFam };

		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	// /注销新生儿登记信息
	public Object updateOptionNeo(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbenm_id = jsonObject.getString("tbenm_id");
//		String tbend_id = jsonObject.getString("tbend_id");
		
		String reserve2 = jsonObject.getString("reserve2");// 新生的男孩数
		String reserve3 = jsonObject.getString("reserve3");// 新生的女孩数
		reserve2 = reserve2.equals("") ? "0" : reserve2;
		reserve3 = reserve3.equals("") ? "0" : reserve3;
		String ms = jsonObject.getString("tbfi_children_mnums");// 原来新生男孩数【用户直接修改当前的家庭男孩数】
		String fs = jsonObject.getString("tbfi_children_fnums");// 原来新生女孩数
		ms = ms.equals("") ? "0" : ms;
		fs = fs.equals("") ? "0" : fs;
		int reserve2s = (Integer.parseInt(ms)-Integer.parseInt(reserve2))>0 ? Integer.parseInt(ms)-Integer.parseInt(reserve2) : 0;//注销之后的新生的男孩数
		int reserve3s = (Integer.parseInt(fs)-Integer.parseInt(reserve3))>0 ? Integer.parseInt(fs)-Integer.parseInt(reserve3) : 0;;//注销之后的新生的女孩数
		int num=reserve2s+reserve3s;
		
		// 更新家庭人数信息
		String m = jsonObject.getString("tbfi_children_mnum");// 男孩数【用户直接修改当前的家庭男孩数】
		String f = jsonObject.getString("tbfi_children_fnum");// 女孩数
		m = m.equals("") ? "0" : m;
		f = f.equals("") ? "0" : f;
		int mss = (Integer.parseInt(m)-Integer.parseInt(reserve2))>0 ? Integer.parseInt(m)-Integer.parseInt(reserve2) : 0;//注销之后的新生的男孩数
		int fss = (Integer.parseInt(f)-Integer.parseInt(reserve3))>0 ? Integer.parseInt(f)-Integer.parseInt(reserve3) : 0;;//注销之后的新生的女孩数
		int c = mss + fss;// 家庭子女数
		
		String sqlup=" update tbe_neonate_main set reserve2="+reserve2s+",reserve3="+reserve3s+",tbenm_gravida_num="+num+" where tbenm_id = '"
		+ tbenm_id + "'";
		
		String sqlFam = "update tb_family_base set tbfi_children_mnum = " + mss
		+ ",tbfi_children_fnum = " + fss + ",tbfi_children_num=" + c
		+ " where (tbe_id = '" +jsonObject.getString("tbe_id")+ "' or tbe_spouse_id='"+jsonObject.getString("tbe_id")+"')";

		String[] sqls = { sqlup,sqlFam };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"注销成功\"";
	}

	/**
	 * 晚婚+晚育 wangli
	 */
	public Object updatLateData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("tbe_id");
		String reserve4 = jsonObject.getString("reserve4");

		String sqle = "update tb_employee set tbe_is_late_birth = '"
				+ reserve4 + "' where tbe_id= '" + id + "'";
		//晚婚+晚育[放在新生儿表中]
		String sqlneo = "update tbe_neonate_main set reserve4 = '"+reserve4+"' where tbenm_id= '" + jsonObject.getString("tbenm_id")  + "'";

		String[] sqls = { sqle,sqlneo };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	/**
	 * 描述：expData 人员导出
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));

		StringBuffer buff = new StringBuffer();
		buff.append("select e.reserve1,e.tbe_name,e.tbe_idcard,f.tbfi_phone,")
			.append("f.tbfi_address,f.tbfi_spouse_name,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,case p.tbepr_option when '0' then '怀孕' when '1' then '等孕' else '无' end,")
			.append("p.tbepr_ptime,case n.tbenm_plan_status when '0' then '计划内一胎' when '1' then '计划内二胎' else ' ' end,n.tbenm_gravida_num,n.tbenm_birth_time,if(n.reserve4='1','是','否'),p.tbepr_memo")
			.append(" from tbe_pregnancy_record p LEFT JOIN tbe_neonate_main n ON p.tbepr_id=n.tbepr_id")
			.append(" LEFT JOIN tb_family_base f ON f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id")
			.append(" where (n.reserve5 is NULL or n.reserve5 <> '1') ");//reserve5为1，不是新生儿状态
		String orgIds = jsonObject.getString("orgIds");// 机构ID串
		String tbe_work_permit = jsonObject.getString("tbe_work_permit");// 身份证号
		String tbe_spell_code = jsonObject.getString("tbe_spell_code");// 名称
		String tbepr_ptime = jsonObject.getString("tbepr_ptime");// 怀孕时间
		String tbenm_plan_status = jsonObject.getString("tbenm_plan_status");// 计划状态
		String tbe_is_late_birth = jsonObject.getString("tbe_is_late_birth");// 是否晚婚+晚育

		if (StringUtils.isBlank(orgIds)) {
			buff.append(" and e.org_id in (").append(user.getCurSubOrgId())
					.append(")");
		} else {
			buff.append(" and e.org_id in (").append(
					user.getCurSubOrgIds(orgIds)).append(")");
		}

		if (StringUtils.isNotBlank(tbe_work_permit)) {
			buff.append(" and e.tbe_idcard like '%").append(tbe_work_permit)
					.append("%'");
		}

		if (StringUtils.isNotBlank(tbe_spell_code)) {
			buff.append(" and e.tbe_name like '%").append(
					tbe_spell_code.toUpperCase()).append("%'");
		}

		if (StringUtils.isNotBlank(tbepr_ptime)) {
			buff.append(" and p.tbepr_ptime >= '").append(tbepr_ptime).append(
					"'");
			// buff.append(" and e.tbepr_ptime <=
			// '").append(tbepr_ptime).append(" 23:59:59'");
		}

		if (StringUtils.isNotBlank(tbenm_plan_status)) {
			if (!tbenm_plan_status.equals("2") && tbenm_plan_status != "") {
				buff.append(" and n.tbenm_plan_status = '").append(
						tbenm_plan_status).append("'");
			}
		}

		if (StringUtils.isNotBlank(tbe_is_late_birth)) {
			if (!tbe_is_late_birth.equals("2") && tbe_is_late_birth != "") {
				buff.append(" and n.reserve4  = '").append(
						tbe_is_late_birth).append("'");
			}
		}
		buff.append("order by tbepr_option asc,p.last_modify_time desc");
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);

		String url = this.getRequest().getSession().getServletContext()
				.getRealPath("/jsp/app/xls/");
		String model = url + "/model/pregnancy.xls";
		String file = url + "/file/pregnancy.xls";
		String rfile = "jsp/app/xls/file/pregnancy.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if (list.size() > 0) {
			XLSUtils.writeXls(list, fto, "孕育状况一览表", 1);// 写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}

		return "\"" + rfile + "\"";
	}

	///筛选选人信息
	public Object listAll(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_name = jsonObject.getString("tbe_name");
		String tbe_idcard = jsonObject.getString("tbe_idcard");
		StringBuffer buff = new StringBuffer();
		buff.append(" from(")
			//夫妻双方只有一个是一汽，看一汽职工【男职工看配偶的年龄.男职工不是离异和丧偶状态,死亡，已离职，已退休状态】,都要看是否有生育条件
			.append("SELECT f.tbfi_id,e.org_id,e.tbe_id,e.tbe_idcard,e.tbe_marital_status,e.tbe_name,e.tbe_sex,e.tbe_birthday,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,f.tbfi_phone, f.tbfi_address,")
			.append("f.tbfi_spouse_name,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.reserve1,e.tbe_is_late_birth ")
			.append("from tb_family_base f LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id where e.tbe_employment_forms <> '31' and e.tbe_status not in('102-5','102-4','102-3') and e.tbe_sex='男'")
			.append(" and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and tbe_is_barrenness='0'")
			.append(" and (f.tbfi_spouse_birthday is null or f.tbfi_spouse_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and f.tbfi_spouse_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR))")
			.append(" and (f.tbfi_spouse_is_join='0' or f.tbfi_spouse_is_join is null)")
			.append(" UNION ")
			//夫妻双方只有一个是一汽，看一汽职工【看女职工的年龄】职工不是离异和丧偶状态,不孕症，死亡，已离职，已退休状态
			.append("SELECT f.tbfi_id,e.org_id,e.tbe_id,e.tbe_idcard,e.tbe_marital_status,e.tbe_name,e.tbe_sex,e.tbe_birthday,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,f.tbfi_phone, f.tbfi_address,")
			.append("f.tbfi_spouse_name,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.reserve1,e.tbe_is_late_birth ")
			.append("from tb_family_base f LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id where e.tbe_employment_forms <> '31' and e.tbe_status not in('102-5','102-4','102-3') and e.tbe_sex='女'" )
			.append(" and e.tbe_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and e.tbe_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR)")
			.append(" and tbe_is_barrenness='0' and the_is_menopause='0' and e.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')")
			.append(" and (f.tbfi_spouse_is_join='0' or f.tbfi_spouse_is_join is null)")
			.append(" UNION ")
			//夫妻双方都是一汽，看女的年龄【女的不为临时工，此情况不会添加】
			.append("SELECT f.tbfi_id,e.org_id,e.tbe_id,e.tbe_idcard,e.tbe_marital_status,e.tbe_name,e.tbe_sex,e.tbe_birthday,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,f.tbfi_phone, f.tbfi_address,")
			.append("f.tbfi_spouse_name,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.reserve1,e.tbe_is_late_birth ")
			.append("from tb_family_base f LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id where e.tbe_sex='女' and e.tbe_status not in('102-5','102-4','102-3')")
			.append(" and e.tbe_birthday <= DATE_SUB(curdate(),INTERVAL 20 YEAR) and e.tbe_birthday >= DATE_SUB(curdate(),INTERVAL 49 YEAR)")
			.append(" and f.tbfi_spouse_is_join='1' and tbe_is_barrenness='0' and the_is_menopause='0'")
			.append(") t where t.org_id in (" + user.getCurSubOrgId() + ") and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320') ");
//				" UNION "+
//				//夫妻双方都是一汽，取男职工【女的为临时工】此情况不会添加
//				"SELECT f.tbfi_id,e.org_id,e.tbe_id,e.tbe_idcard,e.tbe_marital_status,e.tbe_name,e.tbe_sex,e.tbe_birthday," +
//				"(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,f.tbfi_phone, f.tbfi_address," +
//				"f.tbfi_peo_num,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.reserve1,e.tbe_is_late_birth " +
//				"from tb_family_base f LEFT JOIN tb_employee e ON f.tbe_id=e.tbe_id where e.tbe_sex='男'" +
//				" and f.tbfi_spouse_is_join='1' and e.tbe_employment_forms <> '31' and f.tbe_spouse_id in (select tbe_id from tb_employee where tbe_employment_forms = '31')" +
				;//均不可以是未婚状态
		//已婚育龄【20-49】,看妻子的年龄，户主是女性的，直接看户主的年龄
		if(StringUtils.isNotBlank(tbe_name)){
			buff.append(" and tbe_name like '%" + tbe_name + "%'");
		}
		if(StringUtils.isNotBlank(tbe_idcard)){
			buff.append(" and tbe_idcard like '%" + tbe_idcard + "%'");
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			buff.append(" order by t." + sortname + " " + sortorder);
		}
		
		String countSql = "select count(*)" + buff.toString();
		String querySql = "select * " + buff.toString();
		String[] fields = {"tbfi_id","org_id","tbe_id","tbe_idcard","tbe_marital_status","tbe_name","tbe_sex","tbe_birthday","tbe_nation",
			"tbfi_phone","tbfi_address","tbfi_peo_num","tbfi_children_num","tbfi_children_mnum","tbfi_children_fnum","reserve1","tbe_is_late_birth"};
		//System.out.println("querySql:"+querySql);
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}

	/**
	 * 查询明细
	 * @param jsonArray
	 * @return
	 */
	public Object selectDetail(Object jsonArray) {
		
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("FROM(SELECT t.tbe_id,s.org_id,s.reserve1,s.tbe_spell_code,s.tbe_idcard,s.tbe_work_permit,s.tbe_name,s.tbe_sex,s.tbe_age,")
			.append("s.tbe_birthday,s.tbe_position,s.tbe_native_palce,s.tbe_family_phone,s.tbe_address,")
			.append("s.tbe_is_again_birth,s.tbe_is_barrenness,s.tbe_is_late_marriage,s.tbe_is_late_birth,s.tbe_status,t.tbfi_id,t.tbe_spouse_id,")
			.append("t.tbfi_spouse_name,t.tbfi_spouse_sex,t.tbfi_spouse_age,t.tbfi_spouse_birthday,t.tbfi_spouse_idcard,t.tbfi_spouse_department,")
			.append("t.tbfi_spouse_position,t.tbfi_spouse_is_join,t.tbfi_phone,t.tbfi_mobile,t.tbfi_address,t.tbfi_peo_num,t.tbfi_children_num,")
			.append("t.tbfi_children_mnum,t.tbfi_children_fnum,t.tbfi_memo,p.tbepr_option,n.tbenm_plan_status,n.reserve4,n.reserve5")
			.append(" from tbe_pregnancy_record p LEFT JOIN tbe_neonate_main n ON p.tbepr_id=n.tbepr_id ")
			.append(" LEFT JOIN tb_family_base t ON t.tbfi_id=p.tbfi_id LEFT JOIN tb_employee s ON t.tbe_id=s.tbe_id")
			.append(")t where 1=1 and (reserve5 is NULL or reserve5 <> '1') ");

		String[] fields = { "tbe_id", "org_id","reserve1","tbe_spell_code","tbe_idcard", "tbe_work_permit", "tbe_name", "tbe_sex",
				"tbe_age", "tbe_birthday","tbe_position", "tbe_native_palce", "tbe_family_phone", "tbe_address",
				"tbe_is_again_birth", "tbe_is_barrenness", "tbe_is_late_marriage","tbe_is_late_birth", "tbe_status",
				 "tbfi_id", "tbe_spouse_id", "tbfi_spouse_name", "tbfi_spouse_sex","tbfi_spouse_age", "tbfi_spouse_birthday", 
				"tbfi_spouse_idcard", "tbfi_spouse_department","tbfi_spouse_position", "tbfi_spouse_is_join",
				 "tbfi_phone", "tbfi_mobile", "tbfi_address","tbfi_peo_num", "tbfi_children_num", "tbfi_children_mnum",
				 "tbfi_children_fnum", "tbfi_memo","tbepr_option","tbenm_plan_status","reserve4","reserve5"};
		
		
		String type = jsonObject.getString("type");
		String orgIds = jsonObject.getString("orgIds");// 机构ID串
		String tbe_work_permit = jsonObject.getString("tbe_work_permit");// 身份证号
		String tbe_spell_code = jsonObject.getString("tbe_spell_code");// 名称
		String tbepr_ptime = jsonObject.getString("tbepr_ptime");// 怀孕时间
		String tbenm_plan_status = jsonObject.getString("tbenm_plan_status");// 计划状态
		String tbe_is_late_birth = jsonObject.getString("tbe_is_late_birth");// 是否晚婚+晚育

		if (StringUtils.isNotBlank(orgIds)) {
			buff.append(" and t.org_id in (").append(
					user.getCurSubOrgIds(orgIds)).append(")");
		}

		if (StringUtils.isNotBlank(tbe_work_permit)) {
			buff.append(" and t.tbe_idcard like '%").append(
					tbe_work_permit).append("%'");
		}

		if (StringUtils.isNotBlank(tbe_spell_code)) {
			buff.append(" and t.tbe_name like '%").append(
					tbe_spell_code.toUpperCase()).append("%'");
		}

		if (StringUtils.isNotBlank(tbepr_ptime)) {
			buff.append(" and t.tbepr_ptime >= '").append(tbepr_ptime).append("'");
		}

		if (StringUtils.isNotBlank(tbenm_plan_status)) {
			if (!tbenm_plan_status.equals("2")
					&& tbenm_plan_status != "") {
				buff.append(" and t.tbenm_plan_status = '").append(
						tbenm_plan_status).append("'");
			}
		}

		if (StringUtils.isNotBlank(tbe_is_late_birth)) {
			if (!tbe_is_late_birth.equals("2")&& tbe_is_late_birth != "") {
				buff.append(" and t.reserve4  = '").append(tbe_is_late_birth).append("'");
			}
		}

		if(type.equals("pregnancying"))//等孕
		{
			buff.append(" and t.tbepr_option='1'");
		}
		if(type.equals("pregnancyed"))//怀孕
		{
			buff.append(" and t.tbepr_option='0'");
		}
		if(type.equals("pregnancy"))
		{
			buff.append(" and t.tbepr_option in ('0','1')");
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			buff.append(" order by ").append(sortname).append(" ").append(sortorder);
		}
	
		String countSql = "select count(*) "+buff.toString();
		String querySql = " SELECT * " + buff.toString();
		//System.out.println("Detail:"+querySql);
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}

}